package com.wanxi.dao.impl;

import com.wanxi.dao.UserDao;
import com.wanxi.dto.UserDto;
import com.wanxi.model.UserModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    public boolean findByUsername(UserModel model) {
        String sql = "select * from user where username ='" + model.getUsername() + "'";
        ResultSet query = JDBC.query(sql);
//        解析结果集
//        try {
//            while (query.next()) {
//                return new UserDto();
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//
//        return null;
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public int add(UserModel model) {
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`," +
                " `email`, `enable`, `password`," +
                " `phone`, `sex`," +
                " `hobby`, `birthday`, `remark`) VALUES (" +
                "'" + model.getUsername() + "', '" + model.getAddress() + "', now(), " +
                "'" + model.getEmail() + "', '" + model.getEnable() + "', '" + model.getPassword() + "'," +
                " '" + model.getPhone() + "', '" + model.getSex() + "', " +
                "'" + model.getHobby() + "', '" + model.getBirthday() + "', '');";
        return JDBC.update(sql);
    }

    public int update(UserModel model) {
        String sql = "UPDATE `user` SET " +
                "`username` = '" + model.getUsername() + "', " +
                "`address` = '" + model.getAddress() + "', " +
                "`email` = '" + model.getEmail() + "', " +
                "`enable` = '" + model.getEnable() + "', " +
                "`password` = '" + model.getPassword() + "', " +
                "`phone` = '" + model.getPhone() + "', ";
        if (model.getSex() != null) {
            sql += "`sex` = '" + model.getSex() + "', ";
        }
        sql += "`update_time` =now()," +
                " `hobby` = '" + model.getHobby() + "', " +
                "`birthday` = '" + model.getBirthday() + "'," +
                " `remark` = ''" +
                " WHERE (`id` = '" + model.getId() + "');";
        return JDBC.update(sql);
    }

    public int delete(UserModel model) {

        String sql = "delete from user where id=" + model.getId();
        return JDBC.update(sql);
    }

    public UserDto findById(UserModel model) {
        String sql = "select id,address,username,phone,email,enable,date_format(birthday,'%Y/%m/%d')  birthday,create_time,update_time,hobby from user where id=" + model.getId();
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return UserDto.builder()
                        .id(query.getInt("id"))
                        .username(query.getString("username"))
                        .address(query.getString("address"))
                        .phone(query.getString("phone"))
                        .birthday(query.getString("birthday"))
                        .enable(query.getInt("enable"))
                        .hobby(query.getString("hobby"))
                        .createTime(query.getString("create_time"))
                        .updateTime(query.getString("update_time"))
                        .build();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<UserDto> findAll(UserModel model) {
        String sql = "select id,address,username,sex,phone,email,enable,date_format(birthday,'%Y/%m/%d')  birthday,create_time,update_time,hobby from user where true";
        sql += getSql(model);
//        (pageNo-1)*pageSize,pageSize;
//        sql+=" limit start,size";
        sql += " order by id desc ";
        sql += " limit " + (model.getPage() - 1) * model.getLimit() + "," + model.getLimit();
        ResultSet query = JDBC.query(sql);
        List<UserDto> list = new ArrayList<>();
        try {
            while (query.next()) {
                list.add(UserDto.builder()
                        .id(query.getInt("id"))
                        .username(query.getString("username"))
                        .address(query.getString("address"))
                        .phone(query.getString("phone"))
                        .birthday(query.getString("birthday"))
                        .email(query.getString("email"))
                        .sex(query.getString("sex"))
                        .enable(query.getInt("enable"))
                        .hobby(query.getString("hobby"))
                        .createTime(query.getString("create_time"))
                        .updateTime(query.getString("update_time"))
                        .build());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    private String getSql(UserModel model) {
        String sql = "";
        if (model.getUsername() != null && !"".equals(model.getUsername())) {
            sql += " and username like '%" + model.getUsername() + "%' ";
        }
        if (model.getPhone() != null && !"".equals(model.getPhone())) {
            sql += " and phone like '%" + model.getPhone() + "%'";
        }
        if (model.getEmail() != null && !"".equals(model.getEmail())) {
            sql += " and email like '%" + model.getEmail() + "%'";
        }
        if (model.getAddress() != null && !"".equals(model.getAddress())) {
            sql += " and address like '%" + model.getAddress() + "%'";
        }
        if (model.getEnable() != -1) {
            sql += " and enable = '" + model.getEnable() + "'";
        }
        if (model.getStartCreateTime() != null && !"".equals(model.getStartCreateTime())) {
            sql += " and create_time >='" + model.getStartCreateTime() + "' ";
        }
        if (model.getEndCreateTime() != null && !"".equals(model.getEndCreateTime())) {
            sql += " and create_time <='" + model.getEndCreateTime() + "'";
        }
        if (model.getStartBirthday() != null && !"".equals(model.getStartBirthday())) {
            sql += " and birthday >='" + model.getStartBirthday() + "' ";
        }
        if (model.getEndBirthday() != null && !"".equals(model.getEndBirthday())) {
            sql += " and birthday <='" + model.getEndBirthday() + "'";
        }
        if (model.getHobby() != null && !"".equals(model.getHobby())) {
            String[] hobbys = model.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "  find_in_set('" + hobbys[i] + "',hobby) ";
                } else {
                    sql += " or  find_in_set('" + hobbys[i] + "',hobby) ";
                }
            }
            sql += ") ";
        }
        return sql;
    }

    public int count(UserModel model) {
        String sql = "select count(*) count from user  where true ";
        sql += getSql(model);
        return JDBC.count(sql);
    }

    public UserDto findByUsernameAndPassword(UserModel model) {
        String sql = "select * from user where username ='" + model.getUsername() + "' and password ='" + model.getPassword() + "'";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return new UserDto();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
